{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2025-09-15T10:33:25.719586Z",
     "start_time": "2025-09-15T10:33:24.949668Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "path = 'D:/2506A/monty03/day16/file/'"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# 数据透视表",
   "id": "b9cf4811c91f701d"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T10:40:07.178295Z",
     "start_time": "2025-09-15T10:40:07.103235Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 基本透视表\n",
    "df = pd.read_excel(path + '透视.xlsx')\n",
    "\n",
    "# 基本透视：按部门和销售人员分组，计算数量和金额的平均值\n",
    "result = pd.pivot_table(df, values=['数量','金额'], index=['部门','销售人员'],columns='所属区域',aggfunc=['sum','mean'])\n",
    "print(result)"
   ],
   "id": "c9cd35d8054f7580",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              sum                                                            \\\n",
      "               数量                                          金额                 \n",
      "所属区域           四川       山东       河北       河南       湖北      四川     山东     河北   \n",
      "部门  销售人员                                                                      \n",
      "办公室 郑浪    12760.0      NaN   3780.0  13980.0   1270.0    64.0    NaN   22.0   \n",
      "客服  蒋波    14777.0    750.0   4736.0  32036.0  28148.0    93.0   15.0   68.0   \n",
      "    赵温江   17028.0  15726.0  14577.0  11468.0  16550.0    59.0   71.0   71.0   \n",
      "财务  冯文    11746.0   5000.0   6033.0  11918.0   9364.0   239.0   76.0   99.0   \n",
      "    熊牧     2704.0  18975.0      NaN   6084.0    700.0    27.0  216.0    NaN   \n",
      "销售  李平平    9697.0   1688.0   1264.0   2986.0   7239.0   477.0   88.0  109.0   \n",
      "    王松    23693.0   4815.0  10949.0  13564.0  13859.0  1168.0  312.0  404.0   \n",
      "    郑浪     3320.0    500.0      NaN      NaN  10910.0    21.0    8.0    NaN   \n",
      "\n",
      "                               mean                                         \\\n",
      "                                 数量                                          \n",
      "所属区域         河南     湖北           四川           山东           河北           河南   \n",
      "部门  销售人员                                                                     \n",
      "办公室 郑浪     66.0   48.0  1160.000000          NaN   945.000000  1270.909091   \n",
      "客服  蒋波    220.0  260.0   869.235294   250.000000   364.307692   781.365854   \n",
      "    赵温江    99.0   90.0  1419.000000  1310.500000  1325.181818   637.111111   \n",
      "财务  冯文    281.0  203.0   286.487805   384.615385   354.882353   243.224490   \n",
      "    熊牧     61.0   24.0   540.800000   499.342105          NaN   507.000000   \n",
      "销售  李平平   236.0  176.0   110.193182    99.294118    66.526316    67.863636   \n",
      "    王松    591.0  714.0   115.014563    87.545455   145.986667   130.423077   \n",
      "    郑浪      NaN   20.0   830.000000   500.000000          NaN          NaN   \n",
      "\n",
      "                                                                         \n",
      "                             金额                                          \n",
      "所属区域               湖北        四川        山东        河北        河南        湖北  \n",
      "部门  销售人员                                                                 \n",
      "办公室 郑浪     141.111111  5.818182       NaN  5.500000  6.000000  5.333333  \n",
      "客服  蒋波     586.416667  5.470588  5.000000  5.230769  5.365854  5.416667  \n",
      "    赵温江   1103.333333  4.916667  5.916667  6.454545  5.500000  6.000000  \n",
      "财务  冯文     267.542857  5.829268  5.846154  5.823529  5.734694  5.800000  \n",
      "    熊牧     175.000000  5.400000  5.684211       NaN  5.083333  6.000000  \n",
      "销售  李平平    206.828571  5.420455  5.176471  5.736842  5.363636  5.028571  \n",
      "    王松     107.434109  5.669903  5.672727  5.386667  5.682692  5.534884  \n",
      "    郑浪    2727.500000  5.250000  8.000000       NaN       NaN  5.000000  \n"
     ]
    }
   ],
   "execution_count": 5
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T10:40:26.875890Z",
     "start_time": "2025-09-15T10:40:26.806768Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '透视.xlsx')\n",
    "\n",
    "# 基本透视：按部门和销售人员分组，计算数量和金额的平均值\n",
    "result = pd.pivot_table(df, values=['数量','金额'], index=['部门','销售人员'],aggfunc=['sum','mean'])\n",
    "print(result)"
   ],
   "id": "72e334e47e92ba1b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "            sum               mean          \n",
      "             数量    金额           数量        金额\n",
      "部门  销售人员                                    \n",
      "办公室 郑浪    31790   200   908.285714  5.714286\n",
      "客服  蒋波    80447   656   659.401639  5.377049\n",
      "    赵温江   75349   390  1108.073529  5.735294\n",
      "财务  冯文    44061   898   284.264516  5.793548\n",
      "    熊牧    28463   328   482.423729  5.559322\n",
      "销售  李平平   22874  1086   112.679803  5.349754\n",
      "    王松    66880  3189   117.539543  5.604569\n",
      "    郑浪    14730    49  1636.666667  5.444444\n"
     ]
    }
   ],
   "execution_count": 6
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T10:43:04.768871Z",
     "start_time": "2025-09-15T10:43:04.697378Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '透视.xlsx')\n",
    "\n",
    "# 基本透视：按部门和销售人员分组，计算数量和金额的平均值\n",
    "result = pd.pivot_table(df, values=['数量','金额'], index=['部门','销售人员'],aggfunc='sum',margins=True,margins_name='总计',fill_value=0)\n",
    "print(result)"
   ],
   "id": "f6a737b7e2713723",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              数量    金额\n",
      "部门  销售人员              \n",
      "办公室 郑浪     31790   200\n",
      "客服  蒋波     80447   656\n",
      "    赵温江    75349   390\n",
      "财务  冯文     44061   898\n",
      "    熊牧     28463   328\n",
      "销售  李平平    22874  1086\n",
      "    王松     66880  3189\n",
      "    郑浪     14730    49\n",
      "总计        364594  6796\n"
     ]
    }
   ],
   "execution_count": 10
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 交叉表 crosstab",
   "id": "9358fb97801a43cf"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T10:46:45.702610Z",
     "start_time": "2025-09-15T10:46:45.619722Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '透视.xlsx')\n",
    "# 交叉表默认求的是aggfunc = count\n",
    "result = pd.crosstab(\n",
    "    index=df['部门'],\n",
    "    columns=df['所属区域'],\n",
    "    margins=True,\n",
    "    margins_name='合计',\n",
    ")\n",
    "\n",
    "print(result)"
   ],
   "id": "70bb61feed6af1ca",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所属区域   四川   山东   河北   河南   湖北    合计\n",
      "部门                                 \n",
      "办公室    11    0    4   11    9    35\n",
      "客服     29   15   24   59   63   190\n",
      "财务     46   51   17   61   39   214\n",
      "销售    298   73   94  148  168   781\n",
      "合计    384  139  139  279  279  1220\n"
     ]
    }
   ],
   "execution_count": 13
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T10:49:38.523577Z",
     "start_time": "2025-09-15T10:49:38.446196Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '透视.xlsx')\n",
    "# 计算金额之和\n",
    "result = pd.crosstab(\n",
    "    index=df['部门'],\n",
    "    columns=df['所属区域'],\n",
    "    values = df['金额'],\n",
    "    aggfunc='sum',\n",
    "    margins=True,\n",
    "    margins_name='合计',\n",
    ")\n",
    "\n",
    "print(result)"
   ],
   "id": "9df5521aa412f374",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所属区域      四川     山东     河北      河南      湖北    合计\n",
      "部门                                              \n",
      "办公室     64.0    NaN   22.0    66.0    48.0   200\n",
      "客服     152.0   86.0  139.0   319.0   350.0  1046\n",
      "财务     266.0  292.0   99.0   342.0   227.0  1226\n",
      "销售    1666.0  408.0  513.0   827.0   910.0  4324\n",
      "合计    2148.0  786.0  773.0  1554.0  1535.0  6796\n"
     ]
    }
   ],
   "execution_count": 14
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 使用pandas实现excel的透视功能",
   "id": "b33f46d59b8d8f6"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T11:16:11.523335Z",
     "start_time": "2025-09-15T11:16:11.486863Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 1. 读取多个Sheet\n",
    "df1 = pd.read_excel(path + 'Vlookup.xlsx',sheet_name='花名册')\n",
    "df2 = pd.read_excel(path + 'Vlookup.xlsx',sheet_name='成绩单')\n",
    "\n",
    "# result = pd.merge(df1[['学号','姓名']], df2[['学号','总分']], how='left', on='学号')\n",
    "result = pd.merge(df1, df2, how='left', on='学号')\n",
    "print(result)\n",
    "print('=' * 30)\n",
    "\n",
    "agg_functions = ['sum', 'max', 'min', 'mean']\n",
    "agg_names = ['合计', '最高分', '最低分', '平均分']\n",
    "# 使用交叉表\n",
    "for func,name in zip(agg_functions,agg_names):\n",
    "    data = result.pivot_table(\n",
    "        index='学号',\n",
    "        values=['语文','数学','英语','总分'],\n",
    "        aggfunc= func,\n",
    "        margins=True,\n",
    "        margins_name=name,\n",
    "    )\n",
    "    print(data)\n"
   ],
   "id": "9085c57fa587e34",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   学号   姓名 性别  语文  数学  英语   总分\n",
      "0   1  谭鑫宇  女  67  77  63  207\n",
      "1   2  聂茹凤  女  81  58  65  204\n",
      "2   3  韩耀祖  男  89  60  58  207\n",
      "3   4  李欣桐  女  67  61  52  180\n",
      "4   5  刘千琪  女  78  55  51  184\n",
      "==============================\n",
      "合计:\n",
      "     总分   数学   英语   语文\n",
      "学号                    \n",
      "1   207   77   63   67\n",
      "2   204   58   65   81\n",
      "3   207   60   58   89\n",
      "4   180   61   52   67\n",
      "5   184   55   51   78\n",
      "合计  982  311  289  382\n",
      "最高分:\n",
      "      总分  数学  英语  语文\n",
      "学号                  \n",
      "1    207  77  63  67\n",
      "2    204  58  65  81\n",
      "3    207  60  58  89\n",
      "4    180  61  52  67\n",
      "5    184  55  51  78\n",
      "最高分  207  77  65  89\n",
      "最低分:\n",
      "      总分  数学  英语  语文\n",
      "学号                  \n",
      "1    207  77  63  67\n",
      "2    204  58  65  81\n",
      "3    207  60  58  89\n",
      "4    180  61  52  67\n",
      "5    184  55  51  78\n",
      "最低分  180  55  51  67\n",
      "平均分:\n",
      "        总分    数学    英语    语文\n",
      "学号                          \n",
      "1    207.0  77.0  63.0  67.0\n",
      "2    204.0  58.0  65.0  81.0\n",
      "3    207.0  60.0  58.0  89.0\n",
      "4    180.0  61.0  52.0  67.0\n",
      "5    184.0  55.0  51.0  78.0\n",
      "平均分  196.4  62.2  57.8  76.4\n"
     ]
    }
   ],
   "execution_count": 35
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T11:20:54.605837Z",
     "start_time": "2025-09-15T11:20:54.597173Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 调整列的位置\n",
    "# 将总分放在第一列\n",
    "result = pd.merge(df1, df2, how='left', on='学号')\n",
    "print(result)\n",
    "\n",
    "new_colums = ['总分'] + [col for col in result.columns if col!='总分']\n",
    "result = result[new_colums]\n",
    "print(result)\n",
    "\n"
   ],
   "id": "e0c328b2bd20d667",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   学号   姓名 性别  语文  数学  英语   总分\n",
      "0   1  谭鑫宇  女  67  77  63  207\n",
      "1   2  聂茹凤  女  81  58  65  204\n",
      "2   3  韩耀祖  男  89  60  58  207\n",
      "3   4  李欣桐  女  67  61  52  180\n",
      "4   5  刘千琪  女  78  55  51  184\n",
      "    总分  学号   姓名 性别  语文  数学  英语\n",
      "0  207   1  谭鑫宇  女  67  77  63\n",
      "1  204   2  聂茹凤  女  81  58  65\n",
      "2  207   3  韩耀祖  男  89  60  58\n",
      "3  180   4  李欣桐  女  67  61  52\n",
      "4  184   5  刘千琪  女  78  55  51\n"
     ]
    }
   ],
   "execution_count": 38
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
